package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Courier;
import com.houdoudou.common.DateTimeFunction;

public class CourierDao {

	 Connection conn=null;	
     String table="courier";

		public CourierDao(Connection conn) {
			super();
			this.conn = conn;
		}

		public  void create( Courier courier ) throws SQLException{	
			long result=0;
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = "insert  into courier (telnum,secret,type,belong,name ) " //5个参数
		        + "values "
		        + "(?,?,?,?,?)";
		        	//共47个参数。
		        	pstm=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		        	pstm.setString(1, courier.getTelnum());  		      
		        	pstm.setString(2, courier.getSecret());  
		        	pstm.setInt(3, courier.getType()); 
		        	pstm.setString(4, courier.getBelong());  
		        	pstm.setString(5, courier.getName());  
		        	
					pstm.executeUpdate(); 	
				
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
		
		}
		
		private String getDeleteSql(Courier courier){
			String sql="delete  from courier ";

			int count=0;
			if(! courier.getTelnum().isEmpty()){
				 sql+="where telnum="+ courier.getTelnum();
			}
			
			System.out.print(sql);
			return sql;
		}
		public  void delete(String sql) throws SQLException{	
			
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			  
		        try {
		        	        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
					stm.executeUpdate(sql);
					
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			
		}
		
		
		
		public  int update( String sql ) throws SQLException, Exception{	
			  int result=0;
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 		
	  
			   try {
		
			System.out.print(sql);
			stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			stm.executeUpdate(sql);			
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return result;
		}
		
		
		public static String getUpdateSql(Courier courier) throws Exception{
			  int whereCount=1;		
				int setCount=1;	
				int count=1;
			String sql="update courier  ";	
			
	    	
	    	if(!courier.getSecret().isEmpty()) {			
				if(setCount==1) {sql= sql+" set secret = "+courier.getSecret();
				           }else{sql= sql+" , secret = "+courier.getSecret();}			
				            setCount++;  }
	    	if(!courier.getName().isEmpty()) {			
				if(setCount==1) {sql= sql+" set name = "+courier.getName();
				           }else{sql= sql+" , name = "+courier.getName();}			
				            setCount++;  }
	    
	    	
			if(!courier.getTelnum().isEmpty()) {	
				        if(whereCount==1) {sql= sql+" where telnum = "+courier.getTelnum();  }else{sql= sql+" and  telnum = "+courier.getTelnum(); }  
				          whereCount++; 
		    }else{ throw new Exception("更新送货员，没传电话");}
			return sql;
		}
		 public static  String  getSearchSql(Courier courier){
				String sql="select * from courier";
				int count=1;		
				
				if(!courier.getTelnum().isEmpty()) {	
					if(count==1) {sql= sql+" where telnum = "+courier.getTelnum();
					         }else{	sql= sql+" and telnum = "+courier.getTelnum(); }  
					            count++; return sql; }
		
				if(!courier.getBelong().isEmpty()) {	
					if(count==1) {sql= sql+" where belong = "+courier.getBelong();
					          }else{sql= sql+" and  belong = "+courier.getBelong(); }  
					            count++; }
				if(courier.getType()!=-1) {	
					if(count==1) {sql= sql+" where type = "+courier.getType();
					          }else{sql= sql+" and type = "+courier.getType(); }  
					            count++; }
				
				
			System.out.println(sql);
				return sql;
			}

		public List< Courier> search( String sql) throws SQLException{	
			List< Courier> couriers= new ArrayList<Courier>();
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
		
		        try {        		        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
					rs=stm.executeQuery(sql);
					while(rs.next()){
						Courier courier = new Courier();
					
						courier.setType(rs.getInt("type"));			  
			        	courier.setSecret(rs.getString("secret"));  
			        	
			        	courier.setTelnum(rs.getString("telnum"));
			        	courier.setSecret(rs.getString("secret"));
			        	courier.setName(rs.getString("name"));  
			        	courier.setBelong(rs.getString("belong")); 
			        	
			        	
						couriers.add(courier);
					}
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return couriers;
		}	
		
		 public Courier selectById(String telnum) throws Exception{
			   String sql="select * from courier  where telnum = "+ telnum;
			   List<Courier> couriers= search(sql);
			   Courier courierExist = null;
			   if(couriers.size()>0){ courierExist= couriers.get(0); 
			   }
			   return courierExist;
		   }
}
